IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_ETC_PLANNER_Insert_Xml')
	BEGIN
		DROP Procedure usp_UPDMS_ETC_PLANNER_Insert_Xml
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_ETC_PLANNER_Insert_Xml
**	Desc : 플래너를 일괄로 Insert한다.
**	Test Exec Query : Exec usp_UPDMS_ETC_PLANNER_Insert
**	Called by : Etc_Dac_UPDMS_ETC_PLANNER.cs
**	Program ID : Etc4010b
**	Auth : 송시명
**	Date : 2012-04-09
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_ETC_PLANNER_Insert_Xml]
@ls_xml nvarchar(max) = '<UPDMS></UPDMS>' --루트노드
AS

BEGIN

DECLARE @iXmlHandle int
EXEC sp_xml_preparedocument @iXmlHandle OUTPUT, @ls_xml

--삭제
DELETE FROM UPDMS_ETC_PLANNER
WHERE Category = (
        SELECT TOP 1 Category
          FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_ETC_PLANNER', 2)
          WITH ( Category nvarchar(5) )
      )


INSERT INTO UPDMS_ETC_PLANNER (	
       St_Dt,
       St_Hm,
       Ed_Dt,
       Ed_Hm,
       Category,
       Title,
       Contents,
       Location,
       Create_User,
       Create_Dt,
       Update_User,
       Update_Dt
)
SELECT St_Dt,
       St_Hm,
       Ed_Dt,
       Ed_Hm,
       Category,
       Title,
       REPLACE(ISNULL(Contents, ''),'^n',CHAR(13) + CHAR(10)),
       ISNULL(Location, ''),
       Create_User,
       GETDATE(),
       Create_User,
       GETDATE()
  FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_ETC_PLANNER', 2)
  WITH (
       St_Dt nvarchar(10),
       St_Hm nvarchar(5),
       Ed_Dt nvarchar(10),
       Ed_Hm nvarchar(5),
       Category nvarchar(5),
       Title nvarchar(200),
       Contents nvarchar(2000),
       Location nvarchar(100),
       Create_User nvarchar(20)
       ) X
 WHERE X.St_Dt IS NOT NULL
   AND X.Ed_Dt IS NOT NULL
   AND X.Category IS NOT NULL
   AND X.Title IS NOT NULL

--메모리 해제
EXEC sp_xml_removedocument @iXmlHandle

END

GO